package utils;

import entity.ExcelEntity;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: excel
 * @description: excel工具
 * @author: liu denghui
 * @create: 2019/11/22 00:06
 */
public class ExcelUtil {
    /**
     * 根据excel文件获取list
     *
     * @param path 路径
     * @return 数组
     * @throws Exception
     */
    public static List<ExcelEntity> readXls(String path) throws Exception {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        List<ExcelEntity> result = new ArrayList<ExcelEntity>();
        // 循环当前页，并处理当前循环页
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

        // 处理当前页，循环读取每一行 rowNum:从最后一行开始读取
        for (int rowNum = hssfSheet.getLastRowNum(); rowNum >=1 ; rowNum--) {
            ExcelEntity entity = new ExcelEntity();
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);

            // 保留两位小数
            DecimalFormat df   = new DecimalFormat("######0.00");

            HSSFCell cell1 = hssfRow.getCell(0);
            entity.setDeviceNumber(cell1.toString());

            HSSFCell cell2 = hssfRow.getCell(1);
            SimpleDateFormat format = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
            entity.setReceiveTime(format.parse(cell2.toString()));

            HSSFCell cell3 = hssfRow.getCell(2);
            entity.setTemperature(Double.parseDouble(cell3.toString()));

            HSSFCell cell4 = hssfRow.getCell(3);
            entity.setTemperatureUnit(cell4.toString());

            HSSFCell cell5 = hssfRow.getCell(4);
            entity.setHumidity(Double.parseDouble(cell5.toString()));

            HSSFCell cell6 = hssfRow.getCell(5);
            entity.setHumidityUnit(cell6.toString());

            HSSFCell cell7 = hssfRow.getCell(6);
            entity.setIllumination(Double.parseDouble(cell7.toString()));

            HSSFCell cell8 = hssfRow.getCell(7);
            entity.setIlluminationUnit(cell8.toString());

            HSSFCell cell9 = hssfRow.getCell(8);
            entity.setSoilTemperature( Double.parseDouble(df.format(Double.parseDouble(cell9.toString()) + Double.parseDouble(df.format(Math.random()*0.08+0.01)))));

            HSSFCell cell10 = hssfRow.getCell(9);
            entity.setSoilTemperatureUnit(cell10.toString());

            HSSFCell cell11 = hssfRow.getCell(10);
            entity.setSoilMoisture(Double.parseDouble(cell11.toString()));

            HSSFCell cell12 = hssfRow.getCell(11);
            entity.setSoilMoistureUnit(cell12.toString());

            HSSFCell cell13 = hssfRow.getCell(12);
            entity.setSoilConductivity(Double.parseDouble(cell13.toString()));

            HSSFCell cell14 = hssfRow.getCell(13);
            entity.setSoilConductivityUnit(cell14.toString());

            HSSFCell cell15 = hssfRow.getCell(14);
            entity.setSoilPH( Double.parseDouble(df.format(Double.parseDouble(cell15.toString()) + Double.parseDouble(df.format(Math.random()*0.08+0.01)))));

            HSSFCell cell16 = hssfRow.getCell(15);
            entity.setSoilPHUnit(cell16.toString());

            HSSFCell cell17 = hssfRow.getCell(16);
            entity.setSoilNitrogen( Double.parseDouble(df.format(Double.parseDouble(cell17.toString()) + Double.parseDouble(df.format(Math.random()*0.98+0.01)))));

            HSSFCell cell18 = hssfRow.getCell(17);
            entity.setSoilNitrogenUnit(cell18.toString());

            result.add(entity);
        }
        return result;
    }

    /**
     * 生成表格
     *
     * @param path 生成文件路径级名称
     * @param list 数据list
     */
    public static void buildStudentExcel1(String path, List<ExcelEntity> list) {
        //创建一个excel对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet
        HSSFSheet sheet = wb.createSheet("sheet");
        //创建新行
        HSSFRow row1 = sheet.createRow(0);
        //创建新列

        //创建头部栏
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue(ExcelEntity.DEVICE_NUMBER);
        HSSFCell cell2 = row1.createCell(1);
        cell2.setCellValue(ExcelEntity.RECEIVE_TIME);
        HSSFCell cell3 = row1.createCell(2);
        cell3.setCellValue(ExcelEntity.TEMPERATURE);
        HSSFCell cell4 = row1.createCell(3);
        cell4.setCellValue(ExcelEntity.TEMPERATURE_UNIT);
        HSSFCell cell5 = row1.createCell(4);
        cell5.setCellValue(ExcelEntity.HUMIDITY);
        HSSFCell cell6 = row1.createCell(5);
        cell6.setCellValue(ExcelEntity.HUMIDITY_UNIT);
        HSSFCell cell7 = row1.createCell(6);
        cell7.setCellValue(ExcelEntity.ILLUMINATION);
        HSSFCell cell8 = row1.createCell(7);
        cell8.setCellValue(ExcelEntity.ILLUMINATION_UNIT);
        HSSFCell cell9 = row1.createCell(8);
        cell9.setCellValue(ExcelEntity.SOIL_TEMPERATURE);
        HSSFCell cell10 = row1.createCell(9);
        cell10.setCellValue(ExcelEntity.SOIL_TEMPERATURE_UNIT);
        HSSFCell cell11 = row1.createCell(10);
        cell11.setCellValue(ExcelEntity.SOIL_MOISTURE);
        HSSFCell cell12 = row1.createCell(11);
        cell12.setCellValue(ExcelEntity.SOIL_MOISTURE_UNIT);
        HSSFCell cell13 = row1.createCell(12);
        cell13.setCellValue(ExcelEntity.SOIL_CONDUCTIVITY);
        HSSFCell cell14 = row1.createCell(13);
        cell14.setCellValue(ExcelEntity.SOIL_CONDUCTIVITY_UNIT);
        HSSFCell cell15 = row1.createCell(14);
        cell15.setCellValue(ExcelEntity.SOIL_PH);
        HSSFCell cell16 = row1.createCell(15);
        cell16.setCellValue(ExcelEntity.SOIL_PH_UNIT);
        HSSFCell cell17 = row1.createCell(16);
        cell17.setCellValue(ExcelEntity.SOIL_NITROGEN);
        HSSFCell cell18 = row1.createCell(17);
        cell18.setCellValue(ExcelEntity.SOIL_NITROGEN_UNIT);

        //写入list数据
        int j=1;
        for (int i=list.size()-1; i>=0; i--){
            HSSFRow row = sheet.createRow(j);
            cell1 = row.createCell(0);
            cell1.setCellValue(list.get(i).getDeviceNumber());
            cell2 = row.createCell(1);
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String nowTime = sd.format(list.get(i).getReceiveTime());
            cell2.setCellValue(nowTime);
            cell3 = row.createCell(2);
            cell3.setCellValue(list.get(i).getTemperature());
            cell4 = row.createCell(3);
            cell4.setCellValue(list.get(i).getTemperatureUnit());
            cell5 = row.createCell(4);
            cell5.setCellValue(list.get(i).getHumidity());
            cell6 = row.createCell(5);
            cell6.setCellValue(list.get(i).getHumidityUnit());
            cell7 = row.createCell(6);
            cell7.setCellValue(list.get(i).getIllumination());
            cell8 = row.createCell(7);
            cell8.setCellValue(list.get(i).getIlluminationUnit());
            cell9 = row.createCell(8);
            cell9.setCellValue(list.get(i).getSoilTemperature());
            cell10 = row.createCell(9);
            cell10.setCellValue(list.get(i).getSoilTemperatureUnit());
            cell11 = row.createCell(10);
            cell11.setCellValue(list.get(i).getSoilMoisture());
            cell12 = row.createCell(11);
            cell12.setCellValue(list.get(i).getSoilMoistureUnit());
            cell13 = row.createCell(12);
            cell13.setCellValue(list.get(i).getSoilConductivity());
            cell14 = row.createCell(13);
            cell14.setCellValue(list.get(i).getSoilConductivityUnit());
            cell15 = row.createCell(14);
            cell15.setCellValue(list.get(i).getSoilPH());
            cell16 = row.createCell(15);
            cell16.setCellValue(list.get(i).getSoilPHUnit());
            cell17 = row.createCell(16);
            cell17.setCellValue(list.get(i).getSoilNitrogen());
            cell18 = row.createCell(17);
            cell18.setCellValue(list.get(i).getSoilNitrogenUnit());
            j++;
        }

        //生成文件
        FileOutputStream fStream = null;
        try {
            fStream = new FileOutputStream(path);
            wb.write(fStream);
            System.out.println("生成完毕");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
